<?php

require_once ROOT_PATH . '/lib/confs/Conf.php';
require_once ROOT_PATH . '/lib/dao/DMLFunctions.php';
require_once ROOT_PATH . '/lib/dao/SQLQBuilder.php';
require_once ROOT_PATH . '/lib/common/CommonFunctions.php';
require_once ROOT_PATH . '/lib/common/UniqueIDGenerator.php';
require_once ROOT_PATH . '/lib/common/SearchObject.php';
require_once ROOT_PATH . '/lib/models/hrfunct/EmpInfo.php';



class Surveys {

	const TABLE_NAME = 'hs_hr_surveys';
	const TABLE_QUESTION = 'hs_hr_sur_questions';
	const TABLE_OPTION = 'hs_hr_sur_options';
	const TABLE_ANSWER = 'hs_hr_sur_answers';
	const DB_FIELD_QUESTION_ID = 'question_id';
	const DB_FIELD_OPTION_ID = 'option_id';
	const DB_FIELD_ANSWER_ID = 'answer_id';
	
	const DB_FIELD_SURVEY_ID = 'survey_id';
	const DB_FIELD_SURVEY_NAME = 'survey_name';
	const DB_FIELD_SURVEY_DESCRIPTION = 'survey_description';
	const DB_FIELD_SURVEY_FROM_DATE = 'survey_from_date';
	const DB_FIELD_SURVEY_TO_DATE = 'survey_to_date';
	const DB_FIELD_SURVEY_THANKS = 'survey_thanks';
	const DB_FIELD_SURVEY_ACTIVE = 'survey_active';
	const DB_FIELD_SURVEY_OWNER = 'survey_owner';
	const DB_FIELD_SURVEY_TYPE = 'survey_type';
	const DB_FIELD_SURVEY_RESULTS_VIEW = 'results_view';
	const DB_FIELD_USERS = 'survey_users';
	const DB_FIELD_SURVEY_EMAIL_CONTENT = 'survey_email_content';
	const DB_FIELD_SURVEY_RESEND_EMAIL = 'survey_resend_email';
	const DB_FIELD_SURVEY_SENT_EMAIL = 'survey_sent_email';
	const DB_FIELD_SURVEY_ACTIVE_EMAIL = 'survey_active_email';
	
	private $survey_id;
	private $survey_name;
	private $survey_description;
	private $survey_from_date;
	private $survey_to_date;
	private $survey_thanks;
	private $survey_active;
	private $survey_owner;
	private $survey_type;
	private $results_view;
	private $survey_users;
	private $survey_email_content;
	private $survey_resend_email;
	private $survey_sent_email;
	private $survey_active_email;
	
	public function __construct($survey_id = null) {
		$this->survey_id = $survey_id;
	}

	public function setId($id) {
		$this->survey_id = $id;
	}
	
	public function setSurveyName($name) {
		$this->survey_name = $name;
	}

	public function setSurveyDescription($description) {
		$this->survey_description = $description;
	}
	
	public function setSurveyFromDate($fromDate) {
		$this->survey_from_date = $fromDate;
	}
	
	public function setSurveyToDate($toDate) {
		$this->survey_to_date = $toDate;
	}
	
	public function setSurveyThanks($thanks) {
		$this->survey_thanks = $thanks;
	}
	
	public function setSurveyActive($active) {
		$this->survey_active = $active;
	}
	
	public function setSurveyOwner($owner) {
		$this->survey_owner = $owner;
	}
	
	public function setSurveyResultsView($view) {
		$this->results_view = $view;
	}
	
	public function setSurveyType($type) {
		$this->survey_type = $type;
	}

	public function setSurveyUsers($survey_users){
		$this->survey_users = $survey_users;
	}
	
	public function setSurveyEmailContent($emailContent){
		$this->survey_email_content = $emailContent;
	}
	
	public function setSurveyResendEmail($resend){
		$this->survey_resend_email = $resend;
	}
	
	public function setSurveySentEmail($sentEmail){
		$this->survey_sent_email = $sentEmail;
	}

	public function setSurveyActiveEmail($activeEmail){
		$this->survey_active_email = $activeEmail;
	}

	public function getId() {
		return $this->survey_id;
	}

	public function getSurveyName() {
		return $this->survey_name;
	}
	
	public function getSurveyDescription() {
		return $this->survey_description;
	}
	
	public function getSurveyFromDate() {
		return $this->survey_from_date;
	}
	
	public function getSurveyToDate() {
		return $this->survey_to_date;
	}
	
	public function getSurveyThanks() {
		return $this->survey_thanks;
	}
	
	public function getSurveyActive() {
		return $this->survey_active;
	}
	
	public function getSurveyOwner() {
		return $this->survey_owner;
	}
	
	public function getSurveyType() {
		return $this->survey_type;
	}
	
	public function getSurveyResultsView() {
		return $this->results_view;
	}

	public function getSurveyUsers(){
		return $this->survey_users;
	}
	
	public function getSurveyEmailContent(){
		return $this->survey_email_content;
	}
	
	public function getSurveyResendEmail(){
		return $this->survey_resend_email;
	}
	
	public function getSurveySentEmail(){
		return $this->survey_sent_email;
	}

	public function getSurveyActiveEmail(){
		return $this->survey_active_email;
	}

    public function save() {
		if (isset($this->survey_id)) {
			return $this->_update();
		} else {
			return $this->_insert();
		}
    }

	public static function delete($ids) {

		$count = 0;
		if (!is_array($ids)) {
			throw new SurveysException("Invalid parameter to delete(): ids should be an array", SurveysException::INVALID_PARAMETER);
		}
		$arrayquestion = array();
		$arrayoption = array();
		$arrayanswer = array();
		$i=0;
		$j=0;
		$k=0;
		foreach ($ids as $id) {
			if (!CommonFunctions::isValidId($id)) {
				throw new SurveysException("Invalid parameter to delete(): id = $id", SurveysException::INVALID_PARAMETER);
			}
			$questions = Questions::getQuestionsBySurvey($id);
			if(count($questions)>0){								
				foreach ($questions as $question){					
					$arrayquestion[$i]=$question->getId();
					$i++;
					$options = Options::getOptionsByQuestions($question->getId());
					if(count($options)>0){								
						foreach ($options as $option){
							$arrayoption[$j]= $option->getId();
							$j++;
						}		
					}
				}
			}
			$answers = Answers::getAnswersBySurveyId($id);
			if(count($answers)>0){											
				foreach ($answers as $answer){
					$arrayanswer[$k]= $answer->getId();
					$k++;
				}		
			}
		}
		
		if (!empty($ids)) {

			$sql = sprintf("DELETE FROM %s WHERE %s IN (%s)", self::TABLE_NAME,
			                self::DB_FIELD_SURVEY_ID, implode(",", $ids));

			$conn = new DMLFunctions();
			
			$result = $conn->executeQuery($sql);
			if ($result) {
				$count = mysql_affected_rows();
			}
			
		}
		
		if(count($arrayquestion)>0){
			$sql = sprintf("DELETE FROM %s WHERE %s IN (%s)", self::TABLE_QUESTION,
			                self::DB_FIELD_QUESTION_ID, implode(",", $arrayquestion));
			$conn = new DMLFunctions();
			$result = $conn->executeQuery($sql);
		}
		
		if(count($arrayoption)>0){
			$sql = sprintf("DELETE FROM %s WHERE %s IN (%s)", self::TABLE_OPTION,
			                self::DB_FIELD_OPTION_ID, implode(",", $arrayoption));
			$conn = new DMLFunctions();
			$result = $conn->executeQuery($sql);
		}
		
		if(count($arrayanswer)>0){
			$sql = sprintf("DELETE FROM %s WHERE %s IN (%s)", self::TABLE_ANSWER,
			                self::DB_FIELD_ANSWER_ID, implode(",", $arrayanswer));
			$conn = new DMLFunctions();
			$result = $conn->executeQuery($sql);
		}
		
		return $count;
	}
	
	public static function isNameExisted($surveyName,$surveyId=""){
		$selectCondition[0] = "(". self::DB_FIELD_SURVEY_NAME ." = '". trim($surveyName)."' )";	
		$selectCondition[1] = "(". self::DB_FIELD_SURVEY_ID ." != '".$surveyId."' )";	
		$surveys = self::_getList($selectCondition);
		if(count($surveys)>0){
			return true;
		}
		/*
		if(count($surveys)>0){
			$newname = strtolower(htmlspecialchars(str_replace(" ", "",trim($surveyName))));
					
			foreach($surveys as $survey){					
				$name = strtolower(htmlspecialchars(str_replace(" ", "",trim($survey->getSurveyName()))));	
				if(strcmp($surveyName,$survey->getSurveyName())==0){
					return true;
				}
			}
		}else{
			return false;
		}
*/
		return false;
	}

	public static function getAll() {
		return self::_getList();
	}
	
	public static function getPagingAll($page) {
		$index = $page*10;
		$selectLimit = "$index,10";
		$selectOrderBy = self::DB_FIELD_SURVEY_NAME;
		return self::_getList(null,$selectOrderBy,null, $selectLimit);
	}
	public static function getMaxId() {
		$sql = sprintf("SELECT MAX(survey_id) AS NEW_ID FROM %s ", self::TABLE_NAME);
		
			$conn = new DMLFunctions();
			$result = $conn->executeQuery($sql);
			 $max_venue = mysql_result($result,0,0);  
		
		return $max_venue;
	}
	public static function getMaxId2() {	
		$surveys = self::_getList();
		$maxId = 1;
		if(count($surveys)>0){
			foreach($surveys as $survey){
				if($survey->getId()> $maxId){
					$maxId = $survey->getId();
				}
			}
		}
		return $maxId;
	}

	public static function getDashBoard($limit)
	 {
	 	$selectLimit = $limit;
	 	$oderBy =  self::DB_FIELD_SURVEY_ID;
	 	$selectOrder = "DESC";
		return self::_getList(null,$oderBy,$selectOrder,$selectLimit);
	}
	
	
	public static function getResults($surveyId)
	 {	
	 	$selectCondition[0] = "(". self::DB_FIELD_SURVEY_ID ." = ". $surveyId." )";	
		return self::_getList($selectCondition);
	}
	
	public static function getPublicResults()
	 {
	 	//$selectCondition[0] = "(". self::DB_FIELD_SURVEY_ID ." = ". $surveyId." )";	
		return self::_getList();
	}
	
	public static function getListActive() {
		$selectCondition[0] = self::DB_FIELD_SURVEY_ACTIVE." = 1";
		$selectCondition[1] = "( ".self::DB_FIELD_SURVEY_FROM_DATE." <= CURRENT_DATE  AND CURRENT_DATE <= ".self::DB_FIELD_SURVEY_TO_DATE." )";		
		return self::_getList($selectCondition);
	}
	
	public static function getPublicList() {
		$userid = $_SESSION['empID'];
		$selectCondition[0] = self::DB_FIELD_SURVEY_RESULTS_VIEW." = 1"." OR " .self::DB_FIELD_SURVEY_OWNER." = $userid";	
		return self::_getList($selectCondition);
	}
	
	public static function getSurvey($survey_id) {
		$selectCondition[0] = "(". self::DB_FIELD_SURVEY_ID ." = ". $survey_id." )";		
		$surveyList = self::_getList($selectCondition);
		if(count($surveyList)>0){
		return $surveyList[0];
		}else{
			$survey = new Surveys();
			return $survey;
		}
	}

	private static function _getList($selectCondition = null,$selectOrderBy=null,$selectOrder=null, $selectLimit=null) {
		$fields[0] = self::DB_FIELD_SURVEY_ID;
		$fields[1] = self::DB_FIELD_SURVEY_NAME;
		$fields[2] = self::DB_FIELD_SURVEY_DESCRIPTION;
		$fields[3] = self::DB_FIELD_SURVEY_FROM_DATE;
		$fields[4] = self::DB_FIELD_SURVEY_TO_DATE;
		$fields[5] = self::DB_FIELD_SURVEY_THANKS;
		$fields[6] = self::DB_FIELD_SURVEY_ACTIVE;
		$fields[7] = self::DB_FIELD_SURVEY_OWNER;
		$fields[8] = self::DB_FIELD_SURVEY_TYPE;		
		$fields[9] = self::DB_FIELD_SURVEY_RESULTS_VIEW;
		$fields[10] = self::DB_FIELD_USERS;
		$fields[11] = self::DB_FIELD_SURVEY_EMAIL_CONTENT;
		$fields[12] = self::DB_FIELD_SURVEY_RESEND_EMAIL;
		$fields[13] = self::DB_FIELD_SURVEY_SENT_EMAIL;
		$fields[14] = self::DB_FIELD_SURVEY_ACTIVE_EMAIL;
		
		
		
		$tables[0] = self::TABLE_NAME ;
		
		$sqlBuilder = new SQLQBuilder();
		$sql = $sqlBuilder->simpleSelect($tables[0],$fields,$selectCondition,$selectOrderBy,$selectOrder, $selectLimit);
		//die($sql);
		$actList = array();

		$conn = new DMLFunctions();
		$result = $conn->executeQuery($sql);

		while ($result && ($row = mysql_fetch_assoc($result))) {
			$actList[] = self::_createFromRow($row);
		}
		return $actList;
	}

	private function _insert() {
				
		//$fields[0] = self::DB_FIELD_SURVEY_ID;
		$fields[0] = self::DB_FIELD_SURVEY_NAME;
		$fields[1] = self::DB_FIELD_SURVEY_DESCRIPTION;
		$fields[2] = self::DB_FIELD_SURVEY_FROM_DATE;
		$fields[3] = self::DB_FIELD_SURVEY_TO_DATE;
		$fields[4] = self::DB_FIELD_SURVEY_THANKS;
		$fields[5] = self::DB_FIELD_SURVEY_ACTIVE;
		$fields[6] = self::DB_FIELD_SURVEY_OWNER;
		$fields[7] = self::DB_FIELD_SURVEY_TYPE;
		$fields[8] = self::DB_FIELD_SURVEY_RESULTS_VIEW;				
		$fields[9] = self::DB_FIELD_USERS;
		$fields[10] = self::DB_FIELD_SURVEY_EMAIL_CONTENT;
		$fields[11] = self::DB_FIELD_SURVEY_RESEND_EMAIL;
		$fields[12] = self::DB_FIELD_SURVEY_SENT_EMAIL;
		$fields[13] = self::DB_FIELD_SURVEY_ACTIVE_EMAIL;

		//$values[0] = $this->survey_id;
		$values[0] = "'{$this->survey_name}'";
		$values[1] = "'{$this->survey_description}'";		
		$values[2] = (trim($this->survey_from_date) != '') ? "'" . $this->survey_from_date . "'" : "'0000-00-00'";
		$values[3] = (trim($this->survey_to_date) != '') ? "'" . $this->survey_to_date . "'" : "'0000-00-00'";
		$values[4] = "'{$this->survey_thanks}'";
		$values[5] = $this->survey_active;
		$values[6] = "'{$this->survey_owner}'";
		$values[7] = $this->survey_type;
		$values[8] = $this->results_view;
		$values[9] = "'{$this->survey_users}'";
		$values[10] = "'{$this->survey_email_content}'";
		$values[11] = $this->survey_resend_email;
		$values[12] = "'{$this->survey_sent_email}'";
		$values[13] = $this->survey_active_email;
		
		
		$sqlBuilder = new SQLQBuilder();
		$sqlBuilder->table_name = self::TABLE_NAME;
		$sqlBuilder->flg_insert = 'true';
		$sqlBuilder->arr_insert = $values;
		$sqlBuilder->arr_insertfield = $fields;

		$sql = $sqlBuilder->addNewRecordFeature2();

		$conn = new DMLFunctions();

		$result = $conn->executeQuery($sql);
		if (!$result || (mysql_affected_rows() != 1)) {
			throw new OptionsException("Insert failed. ", OptionsException::DB_ERROR);
		}

		return $this->survey_id;
	}

	private function _update() {

		$fields[0] = self::DB_FIELD_SURVEY_ID;
		$fields[1] = self::DB_FIELD_SURVEY_NAME;
		$fields[2] = self::DB_FIELD_SURVEY_DESCRIPTION;
		$fields[3] = self::DB_FIELD_SURVEY_FROM_DATE;
		$fields[4] = self::DB_FIELD_SURVEY_TO_DATE;
		$fields[5] = self::DB_FIELD_SURVEY_THANKS;
		$fields[6] = self::DB_FIELD_SURVEY_ACTIVE;
		$fields[7] = self::DB_FIELD_SURVEY_OWNER;
		$fields[8] = self::DB_FIELD_SURVEY_TYPE;			
		$fields[9] = self::DB_FIELD_SURVEY_RESULTS_VIEW;
		$fields[10] = self::DB_FIELD_USERS;
		$fields[11] = self::DB_FIELD_SURVEY_EMAIL_CONTENT;
		$fields[12] = self::DB_FIELD_SURVEY_RESEND_EMAIL;
		$fields[13] = self::DB_FIELD_SURVEY_SENT_EMAIL;
		$fields[14] = self::DB_FIELD_SURVEY_ACTIVE_EMAIL;
		

		$values[0] = $this->survey_id;
		$values[1] = "'{$this->survey_name}'";	
		$values[2] = "'{$this->survey_description}'";		
		$values[3] = (trim($this->survey_from_date) != '') ? "'" . $this->survey_from_date . "'" : "'0000-00-00'";
		$values[4] = (trim($this->survey_to_date) != '') ? "'" . $this->survey_to_date . "'" : "'0000-00-00'";
		$values[5] = "'{$this->survey_thanks}'";
		$values[6] = $this->survey_active;
		$values[7] = $this->survey_owner;
		$values[8] = $this->survey_type;			
		$values[9] = $this->results_view;
		$values[10] = "'{$this->survey_users}'";
		$values[11] = "'{$this->survey_email_content}'";
		$values[12] = $this->survey_resend_email;
		$values[13] = "'{$this->survey_sent_email}'";
		$values[14] = $this->survey_active_email;
		
		$sqlBuilder = new SQLQBuilder();
		$sqlBuilder->table_name = self::TABLE_NAME;
		$sqlBuilder->flg_update = 'true';
		$sqlBuilder->arr_update = $fields;
		$sqlBuilder->arr_updateRecList = $values;

		$sql = $sqlBuilder->addUpdateRecord1(0);

		$conn = new DMLFunctions();
		$result = $conn->executeQuery($sql);

		if (!$result) {
			throw new OptionsException("Update failed. SQL=$sql", OptionsException::DB_ERROR);
		}
		return $this->survey_id;
	}

    private static function _createFromRow($row) {
    	
    	$survey = new Surveys($row[self::DB_FIELD_SURVEY_ID]);
    	$survey->setId($row[self::DB_FIELD_SURVEY_ID]);
    	$survey->setSurveyName($row[self::DB_FIELD_SURVEY_NAME]);
    	$survey->setSurveyDescription($row[self::DB_FIELD_SURVEY_DESCRIPTION]);    	
    	$survey->setSurveyFromDate($row[self::DB_FIELD_SURVEY_FROM_DATE]);
    	$survey->setSurveyToDate($row[self::DB_FIELD_SURVEY_TO_DATE]);
    	$survey->setSurveyThanks($row[self::DB_FIELD_SURVEY_THANKS]);
    	$survey->setSurveyActive($row[self::DB_FIELD_SURVEY_ACTIVE]);
    	$survey->setSurveyOwner( $row[self::DB_FIELD_SURVEY_OWNER]);
    	$survey->setSurveyType($row[self::DB_FIELD_SURVEY_TYPE]);
    	$survey->setSurveyResultsView($row[self::DB_FIELD_SURVEY_RESULTS_VIEW]);
    	$survey->setSurveyUsers($row[self::DB_FIELD_USERS]);
    	$survey->setSurveyEmailContent($row[self::DB_FIELD_SURVEY_EMAIL_CONTENT]);
    	$survey->setSurveyResendEmail($row[self::DB_FIELD_SURVEY_RESEND_EMAIL]);
    	$survey->setSurveySentEmail($row[self::DB_FIELD_SURVEY_SENT_EMAIL]);
    	$survey->setSurveyActiveEmail($row[self::DB_FIELD_SURVEY_ACTIVE_EMAIL]);
    	return $survey;
    }
   

}

class SurveysException extends Exception {
	const INVALID_PARAMETER = 0;
	const DB_ERROR = 1;
}
?>
